﻿namespace AnyOffice.DAL
{
    using AnyOffice.Model;
    using Microsoft.ApplicationBlocks.Data;
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Text;

    public class NetMeetingSessionsDao
    {
        public bool Delete(SqlConnection conn, int sessionId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("DELETE FROM [dbo].[NetMeeting_Messages] WHERE [SessionId] = @SessionId ");
            builder.Append("DELETE FROM [dbo].[NetMeeting_User] WHERE [SessionId] = @SessionId ");
            builder.Append("DELETE FROM [dbo].[NetMeeting_Sessions] WHERE [SessionId] = @SessionId ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@SessionId", SqlDbType.Int) };
            commandParameters[0].Value = sessionId;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }

        public bool Insert(SqlConnection conn, NetMeetingSessionsData data)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("INSERT [dbo].[NetMeeting_Sessions] ");
            builder.Append("( ");
            builder.Append("\t[SessionName], ");
            builder.Append("\t[FromId], ");
            builder.Append("\t[ToId], ");
            builder.Append("\t[BeginTime], ");
            builder.Append("\t[State] ");
            builder.Append(") ");
            builder.Append("VALUES ");
            builder.Append("( ");
            builder.Append("    \t@SessionName, ");
            builder.Append("\t@FromId, ");
            builder.Append("\t@ToId, ");
            builder.Append("\t@BeginTime, ");
            builder.Append("\t@State ");
            builder.Append(") ");
            SqlParameter[] commandParameters = new SqlParameter[5];
            commandParameters[0] = new SqlParameter("@SessionName", SqlDbType.NVarChar, 100);
            commandParameters[0].Value = data.SessionName;
            commandParameters[1] = new SqlParameter("@FromId", SqlDbType.NVarChar, 20);
            commandParameters[1].Value = data.FromId;
            commandParameters[2] = new SqlParameter("@ToId", SqlDbType.NText);
            commandParameters[2].Value = data.ToId;
            commandParameters[3] = new SqlParameter("@BeginTime", SqlDbType.DateTime);
            commandParameters[3].Value = data.BeginTime;
            commandParameters[4] = new SqlParameter("@State", SqlDbType.Bit);
            commandParameters[4].Value = data.State;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }

        public DataTable SelectAll(SqlConnection conn)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("UPDATE [dbo].[NetMeeting_User] ");
            builder.Append("SET ");
            builder.Append("\t[State]=0 ");
            builder.Append("WHERE ");
            builder.Append("\tDATEDIFF(s,[LastRefreshTime],getdate())>120  ");
            builder.Append("DECLARE @SessionId int ");
            builder.Append("DECLARE Online_Cursor CURSOR FOR  ");
            builder.Append("\t\tSELECT [SessionId] FROM [dbo].[NetMeeting_Sessions] ");
            builder.Append("OPEN Online_Cursor ");
            builder.Append("FETCH NEXT FROM Online_Cursor INTO @SessionId ");
            builder.Append("WHILE @@FETCH_STATUS = 0 ");
            builder.Append("BEGIN ");
            builder.Append("\tUPDATE [dbo].[NetMeeting_Sessions] ");
            builder.Append("\tSET ");
            builder.Append("\t\t[OnlineNums] = (SELECT COUNT(*) FROM [dbo].[NetMeeting_User] WHERE [SessionId] = @SessionId AND [State]=1)  ");
            builder.Append("\tWHERE ");
            builder.Append("\t\t[SessionId] = @SessionId ");
            builder.Append("\tFETCH NEXT FROM Online_Cursor INTO @SessionId ");
            builder.Append("END ");
            builder.Append("CLOSE Online_Cursor ");
            builder.Append("DEALLOCATE Online_Cursor ");
            builder.Append("SELECT ");
            builder.Append("\t[NetMeeting_Sessions].[SessionId], ");
            builder.Append("\t[Users].[UserName] AS FromUserName, ");
            builder.Append("\tdbo.GetUserNames(CAST([NetMeeting_Sessions].[ToId] AS nvarchar(4000))) AS ToUserName, ");
            builder.Append("\t[NetMeeting_Sessions].[ToId], ");
            builder.Append("\t[NetMeeting_Sessions].[SessionName], ");
            builder.Append("\t[NetMeeting_Sessions].[BeginTime], ");
            builder.Append("\tGETDATE() AS DateTimeNow, ");
            builder.Append("\t[NetMeeting_Sessions].[State], ");
            builder.Append("\t[NetMeeting_Sessions].[OnlineNums] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[NetMeeting_Sessions], ");
            builder.Append("\t[dbo].[Users] ");
            builder.Append("WHERE\t ");
            builder.Append("\t[NetMeeting_Sessions].[FromId] = [Users].[UserId] ");
            builder.Append("ORDER BY ");
            builder.Append("\t[NetMeeting_Sessions].[SessionId] ASC ");
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString());
            set.Tables[0].TableName = "NetMeeting_Sessions";
            return set.Tables[0];
        }

        public DataTable SelectBySessionId(SqlConnection conn, int sessionId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SELECT ");
            builder.Append("\t[SessionId], ");
            builder.Append("\t[SessionName], ");
            builder.Append("\t[State] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[NetMeeting_Sessions] ");
            builder.Append("WHERE ");
            builder.Append("\t[SessionId] = @SessionId ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@SessionId", SqlDbType.Int) };
            commandParameters[0].Value = sessionId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "NetMeeting_Sessions";
            return set.Tables[0];
        }

        public bool Switch(SqlConnection conn, int sessionId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("DECLARE @BeginTime datetime ");
            builder.Append("DECLARE @State int ");
            builder.Append("SELECT ");
            builder.Append("\t@BeginTime = [BeginTime], ");
            builder.Append("\t@State = [State] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[NetMeeting_Sessions] ");
            builder.Append("WHERE ");
            builder.Append("\t[SessionId] = @SessionId ");
            builder.Append("IF @BeginTime > GETDATE() ");
            builder.Append("\tBEGIN ");
            builder.Append("\t\tUPDATE [dbo].[NetMeeting_Sessions] ");
            builder.Append("\t\tSET ");
            builder.Append("\t\t\t[State] = 1, ");
            builder.Append("\t\t\t[BeginTime] = GETDATE()\t\t   ");
            builder.Append("\t\tWHERE ");
            builder.Append("\t\t\t[SessionId] = @SessionId\t ");
            builder.Append("\t ");
            builder.Append("\tEND ");
            builder.Append("ELSE ");
            builder.Append("\tBEGIN ");
            builder.Append("\t\tIF @State = 1  ");
            builder.Append("\t\t\tBEGIN\t ");
            builder.Append("\t\t\t\tINSERT [dbo].[NetMeeting_Messages] (SessionId,Color,Content,SendTime) VALUES (@SessionId,'black','会议召集人已经结束会议',GETDATE()) ");
            builder.Append("\t\t\t\tINSERT [dbo].[NetMeeting_Messages] (SessionId,Color,Content,SendTime) VALUES (@SessionId,'black','@CLOSE',GETDATE()) ");
            builder.Append("\t\t\tEND ");
            builder.Append("\t\tELSE ");
            builder.Append("\t\t\tBEGIN ");
            builder.Append("\t\t\t\tDELETE FROM [dbo].[NetMeeting_Messages] WHERE  [SessionId] = @SessionId\tAND [Content] LIKE '%@CLOSE%' ");
            builder.Append("\t\t\t\tINSERT [dbo].[NetMeeting_Messages] (SessionId,Color,Content,SendTime) VALUES (@SessionId,'black','会议继续进行',GETDATE()) ");
            builder.Append("\t\t\tEND ");
            builder.Append("\t\tUPDATE [dbo].[NetMeeting_User] ");
            builder.Append("\t\tSET ");
            builder.Append("\t\t\t[State] = 0 ");
            builder.Append("\t\tWHERE ");
            builder.Append("\t\t\t[SessionId] = @SessionId ");
            builder.Append("\t ");
            builder.Append("\t\tUPDATE [dbo].[NetMeeting_Sessions] ");
            builder.Append("\t\tSET ");
            builder.Append("\t\t\t[State] = ABS([State]-1) ");
            builder.Append("\t\tWHERE ");
            builder.Append("\t\t\t[SessionId] = @SessionId\t ");
            builder.Append("\tEND ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@SessionId", SqlDbType.Int) };
            commandParameters[0].Value = sessionId;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }
    }
}

